﻿using Microsoft.Data.Sqlite;
using SendMailServer.Database;
using System;
using System.Text;
using System.Text.RegularExpressions;

namespace SendMailServer
{
    internal class ExamHelper
    {
        public static QuestionItem GetNextQuestion(string urserName)
        {
            StringBuilder debuggerStr = new StringBuilder();
            Microsoft.Data.Sqlite.SqliteConnection conn = null;
            Microsoft.Data.Sqlite.SqliteCommand cmd = null;
            SqliteDataReader dr = null;
            try
            {
                using (conn = DatabaseHelper.BuildConnection())
                {
                    string sql = "select  count(*) from questionList";
                    cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                    var count = (long)(cmd.ExecuteScalar());
                    debuggerStr.Append("count=" + count + ",");
                    var random = new Random(System.Environment.TickCount);
                    var t = random.Next(0, (int)count) + 1;
                    sql = "select *  from questionList where qid=" + t;
                    debuggerStr.Append("sql=" + sql + ",");
                    cmd.CommandText = sql;
                    dr = cmd.ExecuteReader();

                    if (dr.Read())
                    {
                        QuestionItem itm = new QuestionItem()
                        {
                            qID = (long)(dr["qid"]),
                            qName = dr["qname"] as string,
                            qOptionA = dr["qOptionA"] as string,
                            qOptionB = dr["qOptionB"] as string,
                            rightValue = (long)dr["rightValue"]
                        };

                        return itm;
                    }
                    else
                    {
                        return new QuestionItem()
                        {
                            qID = -1,
                            qName = "发生了错误:" + debuggerStr.ToString()
                        }; ;
                    }
                }
            }
            catch (Exception err)
            {
                return new QuestionItem()
                {
                    qID = -1,
                    qName = "发生了错误:" + err.Message + "," + err.StackTrace
                };
            }
            finally
            {
                if(dr != null)
                {
                    dr.Close();
                }
                if(cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        /// <summary>
        /// 获得一个页面的访问数量，注意这里会对页面加1.
        /// </summary>
        /// <returns></returns>
        public static string GetPageViewCount(string pageName)
        {
            StringBuilder debuggerStr = new StringBuilder();
            Microsoft.Data.Sqlite.SqliteConnection conn = null;
            Microsoft.Data.Sqlite.SqliteCommand cmd = null;
            SqliteDataReader dr = null;
            try
            {
                using (conn = DatabaseHelper.BuildConnection())
                {
                    if (pageName == "JikeAll")
                    {
                        var str = GetPageViewCountOfJike(conn, out cmd, out dr);
                        return "JikeAll," + str;
                    }
                    else
                    {
                        string sql = "select pageCount from pageVisitCount where pageName='" + pageName + "'";
                        cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                        var countObj = (cmd.ExecuteScalar());
                        var count = 0;
                        if (countObj == null || countObj is DBNull)
                        {
                            count = 1;
                            sql = "insert into pageVisitCount(pageCount,pageName) values(1,'" + pageName + "')";
                        }
                        else
                        {
                            count = int.Parse(countObj.ToString()) + 1;
                            sql = "update  pageVisitCount set pageCount=" + count + " where pageName='" + pageName + "'";
                        }
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();

                        return count.ToString() + "," + pageName;
                    }

                }
            }
            catch (Exception err)
            {
                return "GetPageViewCount发生异常:" + err.Message;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }

        }
        public static string GetPageViewCountOfJike(Microsoft.Data.Sqlite.SqliteConnection conn,out Microsoft.Data.Sqlite.SqliteCommand cmd,out SqliteDataReader dr)
        {
            string sql = "select * from pageVisitCount where pageName like 'Jike-%'";
            cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
            dr=cmd.ExecuteReader();
            StringBuilder stringBuilder = new StringBuilder();
            while (dr.Read())
            {
                stringBuilder.Append(dr["pageCount"] + "," + dr["pageName"] + "#");
            }
            return stringBuilder.ToString();
        }
        public static string GetJikeComments(string pageName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            Microsoft.Data.Sqlite.SqliteConnection conn = null;
            Microsoft.Data.Sqlite.SqliteCommand cmd = null;
            SqliteDataReader dr = null;
            try
            {
                using (conn = DatabaseHelper.BuildConnection())
                {
                    string sql = "select * from jikeComments where pageName='" + pageName + "'";
                    cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                    dr= cmd.ExecuteReader();
                    while(dr.Read())
                    {
                        stringBuilder.Append(dr["commentor"] + "," + dr["commentorWord"] + "#");
                    }

                    return stringBuilder.ToString();
                }
            }
            catch (Exception err)
            {
                return "GetJikeComments发生异常:" + err.Message;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        public static string SaveJikeComments(string fullRow)
        {
            try
            {
                int t1 = fullRow.IndexOf(',');
                int t2 = fullRow.IndexOf(',', t1+1);
                var pageName = fullRow.Substring(0, t1);
                var commentor = fullRow.Substring(t1 + 1, t2 - t1 - 1);
                var commentorWord = fullRow.Substring(t2+1);
                return SaveJikeComments(pageName, commentor, commentorWord);
            }
            catch(Exception err)
            {
                return "SaveJikeComments发生异常:" + err.Message;
            }
            
        }
        public static string SaveJikeComments(string pageName,string commentor,string commentorWord)
        {
            Microsoft.Data.Sqlite.SqliteConnection conn = null;
            Microsoft.Data.Sqlite.SqliteCommand cmd = null;
            SqliteDataReader dr = null;
            try
            {
                using (conn = DatabaseHelper.BuildConnection())
                {
                    string sql = "insert into jikeComments values('"+pageName+"','"+commentor+ "','"+ commentorWord + "')";
                    cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                    cmd.ExecuteNonQuery();

                    return "提交成功";
                }
            }
            catch (Exception err)
            {
                return "SaveJikeComments发生异常:" + err.Message;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        public static string GetJikeTagData(string pageName)
        {
            StringBuilder stringBuilder = new StringBuilder();
            Microsoft.Data.Sqlite.SqliteConnection conn = null;
            Microsoft.Data.Sqlite.SqliteCommand cmd = null;
            SqliteDataReader dr = null;
            try
            {
                using (conn = DatabaseHelper.BuildConnection())
                {
                    string sql = "select * from jikeTags where pageName='" + pageName + "'";
                    cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        stringBuilder.Append(dr["tagData"] + "#");
                    }

                    return stringBuilder.ToString();
                }
            }
            catch (Exception err)
            {
                return "GetJikeTagData发生异常:" + err.Message;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        public static string SaveJikeTagData(string fullRow)
        {
            try
            {
                int t1 = fullRow.IndexOf(',');
                int t2 = fullRow.IndexOf(',', t1 + 1);
                var pageName = fullRow.Substring(0, t1);
                var tagData = fullRow.Substring(t1 + 1);
                return SaveJikeTagData(pageName, tagData);
            }
            catch (Exception err)
            {
                return "SaveJikeComments发生异常:" + err.Message;
            }
        }
        public static string SaveJikeTagData(string pageName, string tagData)
        {
            Microsoft.Data.Sqlite.SqliteConnection conn = null;
            Microsoft.Data.Sqlite.SqliteCommand cmd = null;
            SqliteDataReader dr = null;
            try
            {
                using (conn = DatabaseHelper.BuildConnection())
                {
                    string sql = "select  count(*) from jikeTags where pageName='"+pageName+"'";
                    cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                    var count = (long)(cmd.ExecuteScalar());

                    if(count==0)
                        sql = "insert into jikeTags values('" + pageName + "','" + tagData + "')";
                    else
                        sql = "update jikeTags set tagData='"+ tagData + "' where pageName='"+pageName+"'";
                    cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                    cmd.ExecuteNonQuery();

                    return "提交成功";
                }
            }
            catch (Exception err)
            {
                return "SaveJikeComments发生异常:" + err.Message;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        public static string SearchJikeTagData(string tagData)
        {
            StringBuilder stringBuilder = new StringBuilder();
            Microsoft.Data.Sqlite.SqliteConnection conn = null;
            Microsoft.Data.Sqlite.SqliteCommand cmd = null;
            SqliteDataReader dr = null;
            try
            {
                using (conn = DatabaseHelper.BuildConnection())
                {
                    string sql = "select * from jikeTags where tagData like '%" + tagData + "%'";
                    cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        stringBuilder.Append(dr["pageName"] + "#");
                    }

                    return stringBuilder.ToString();
                }
            }
            catch (Exception err)
            {
                return "GetJikeTagData发生异常:" + err.Message;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        public static bool SaveChatGptQuestions(string userName, string chatMsg,out string errorMsg)
        {
            Microsoft.Data.Sqlite.SqliteConnection conn = null;
            Microsoft.Data.Sqlite.SqliteCommand cmd = null;
            SqliteDataReader dr = null;
            errorMsg = null;
            try
            {
                var t = chatMsg.IndexOf("query");
                if (t > 0)
                {
                    var t2 = chatMsg.IndexOf("\",",t);
                    if (t2 >= 0)
                    {
                        chatMsg = chatMsg.Substring(t+7, t2 - t-7);
                    }
                }
                chatMsg = Regex.Replace(chatMsg, "\"", "");
                using (conn = DatabaseHelper.BuildConnection())
                {
                    string sql = "insert into chatgptQuestions(UserName,Question,QuestionDate) values(\"" + userName + "\",\"" + chatMsg + "\",\"" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "\")";
                    cmd = new Microsoft.Data.Sqlite.SqliteCommand(sql, conn);
                    cmd.ExecuteNonQuery();

                    return true;
                }
            }
            catch (Exception err)
            {
                errorMsg = err.Message + ";" + err.StackTrace;
                return false;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
                if (cmd != null)
                {
                    cmd.Dispose();
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            
        }
        
    }
    internal class QuestionItem
    {
        public long qID;
        public string qName;
        public string qOptionA;
        public string qOptionB;
        public byte[] qNamePic;
        public long rightValue;

        public string ToFormatString()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(qID + "$");
            sb.Append(qName + "$");
            sb.Append(qOptionA + "$");
            sb.Append(qOptionB + "$");
            sb.Append(rightValue);
            return sb.ToString();
        }
    }
}
